Sorted!

Notes/Domino 8 Forum

Subject:

Sorted!

Hi Ed

Really appreciate your comments, although I managed to get it running a different way. Here's the code if it helps anyone else. The main thing I seemed to notice was that even though the result of the "execute" statement is set to "nothing" it still couldn't be part of the "while" process. As a result I copied the information into an array and processed that afterwards.

Sub Initialize()

On Error Goto handle_error
REM **************************************************
REM Define temporary variables
REM **************************************************
Dim vWebSession As New notessession
Dim vThisDatabase As notesdatabase

Dim vThisDocument As notesdocument
Dim newDoc As NotesDocument

Dim ses As New LCSession
Dim con As New LCConnection ("oledb")

Dim MyFldList As New LCFieldList
Dim MyFldList2 As New LCFieldList
Dim MyFld As LCField
Dim MyFld2 As LCField
Dim MyFld3 As LCField
Dim MyFld4 As LCField
Dim SQL As String
Dim MyResult As String

Dim count As Integer
Dim X As Integer
Dim Y As Integer

REM **************************************************
REM Initialize variables
REM **************************************************

Set vThisDatabase = vWebSession.CurrentDatabase
'Set vThisDocument = vWebSession.DocumentContext
ses.ClearStatus

'Set connection details
With con
.Provider = "SQLOLEDB"
.Server = "Server"
.Database = "Database"
.Metadata = "TableName"
.userid = "-----"
.password = "-----"
End With

On Error Resume Next
con.Connect
con.writeback = True

If Err = False Then
Else
Print "Connection failed with error " & Err & ": " & Error
Exit Sub
End If

REM **************************************************
REM Do stuff here
REM **************************************************

SQL = "SELECT TransactionID, UserID, projectId, questionnaireId from ProjectDetail"
con.Execute sql,myfldlist

Set myfld = myfldlist.GetField(1)
Set myfld2 = myfldlist.GetField(2)
Set myfld3 = myfldlist.GetField(3)
Set myfld4 = myfldlist.GetField(4)

'Print Open of page
Print "Successfully connected to SQLServer."
Print "<table>"
Print "<tr>"
Print "<td>Row</td>"
Print "<td>Transaction ID</td>"
Print "<td>User ID</td>"
Print "<td>Project ID</td>"
Print "<td>Questionnaire ID</td>"
Print "<td>Notes UNID</td>"

'Check for empty list
If con.Fetch(myfldlist) = 0 Then
Print "No Records Located"
Goto handle_error
End If

'Set initial Value of Y
Y = 0

'Process the list
While (con.Fetch(myfldlist) > 0)

If myfld2.text(0) = "AdminA" Then

Redim Preserve toUpdate(4, Y)

toUpdate(0,Y) = myfld.Text(0) 'Tr ID
toUpdate(1,Y) = myfld2.Text(0) 'User ID
toUpdate(2,Y) = myfld3.Text(0) 'Project ID
toUpdate(3,Y) = myfld4.Text(0) 'Questionnaire ID
toUpdate(4,Y) = Cstr(y+1)

Y = Y+1

End If

Wend

'Loop through all results located
For X = 0 To Y-1
'Create a NotesDocument
Set newDoc = vThisDatabase.CreateDocument

'Populate It
newDoc.transID = toUpdate(0,X)
newDoc.userID = toUpdate(1,X)
newDoc.projectID = toUpdate(2,X)
newDoc.quID = toUpdate(3,X)

'Save it
Call newDoc.Save(True,True)

'Write update back to SQL
con.Execute "UPDATE ProjectDetail SET NotesUNID='"+newDoc.UniversalID+"' WHERE transactionid="+toUpdate(0,X), Nothing

'Print Result
Print "<tr>"
Print "<td>" & toUpdate(4,X) & "</td>"
Print "<td>" & toUpdate(0,X) & "</td>"
Print "<td>" & toUpdate(1,X) & "</td>"
Print "<td>" & toUpdate(2,X) & "</td>"
Print "<td>" & toUpdate(3,X) & "</td>"
Print "<td>" & newDoc.UniversalID & "</td>"
Print "</tr>"
Next

'Print Table Close
Print "</table>"

'Close session
con.Disconnect
Set myfldlist = Nothing
Set myfld = Nothing
Set con = Nothing
Set ses = Nothing

Exit Sub

REM **************************************************
REM Error Handler
REM **************************************************
handle_error:
Dim stat$, errcode As Long, msg$
If ses.Status <> LCSUCCESS Then
ses.Getstatus stat, errcode, msg
If (ses.Status = LCFAIL_EXTERNAL)Then
Print "ODBC message: " & msg & " code #" & Cstr(errcode), 0, _
"error number " & Err & " line " & Erl
Else
Print "Connector message: " & Text, 0, "error number " & _
Err & " line " & Erl
End If
Else
Print Error, 0, "error number " & Err & " line " & Erl
End If
Exit Sub
End Sub

Feedback response number WEBB8ADEWG created by ~Lisa Elreterobu on 10/19/2010